package com.hr.dept.dao.Impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.hr.dept.dao.DeptDao;
import com.hr.dept.domain.Dept;
import com.hr.utils.PageBean;
import com.hr.utils.TxQueryRunner;

/**
 * 部门管理数据库实现层
 * @author 杨谦
 * @date 2015-11-2
 *
 */
public class DeptDaoImpl implements DeptDao{
	private QueryRunner qr = new TxQueryRunner();
	@Override
	public Dept findByDeptID(String dept_ID) {
		// TODO Auto-generated method stub
		String sql = "select * from DEPT_TABLE where dept_ID = ?";
		try {
			return qr.query(sql, new BeanHandler<Dept>(Dept.class), dept_ID);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Dept> queryAllDept() {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from DEPT_TABLE";
			return qr.query(sql, new BeanListHandler<Dept>(Dept.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public void addDept(Dept dept) {
		// TODO Auto-generated method stub
		String sql = "insert into DEPT_TABLE values(?,?,?,?)";
		try {
			qr.update(sql, dept.getDept_ID(),dept.getDept_NAME(),dept.getDept_DESCRIBLE(),dept.getDept_STATE());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public void updateDept(Dept dept) {
		// TODO Auto-generated method stub
		String sql = "update DEPT_TABLE set dept_NAME=?,dept_DESCRIBLE=?,dept_STATE=? where dept_ID=?";
		try {
			qr.update(sql, dept.getDept_NAME(),dept.getDept_DESCRIBLE(),dept.getDept_STATE(),dept.getDept_ID());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public PageBean<Dept> queryAll_Page(int PageCurrent,int PageSize) {
		// TODO Auto-generated method stub
		try {
			PageBean<Dept> pb = new PageBean<Dept>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from DEPT_TABLE";
			Number num = (Number)qr.query(sql, new ScalarHandler());
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from DEPT_TABLE order by dept_id desc)a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<Dept> list_dept = qr.query(sql, new BeanListHandler<Dept>(Dept.class));
			pb.setBeanList(list_dept);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

	@Override
	public void deleteDeptByID(String id) {
		// TODO Auto-generated method stub
		String sql_dept_ID = "update DEPT_USER set DEPT_ID='0' where DEPT_ID=?";
		String sql_user_dept_ID = "update HR_USER set DEPT_ID='0' where DEPT_ID=?";
		String sql = "delete DEPT_TABLE where DEPT_ID=?";
		try {
			qr.update(sql_dept_ID,id);
			qr.update(sql_user_dept_ID, id);
			qr.update(sql,id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}

}
